import xlrd
import xlsxwriter
import logging


# 获取excel内容的工具类 增加生成excel测试报告功能
class Excel(object):
    # 初始化方法
    def __init__(self, type, file_name):
        # 读取excel
        if type == 'r':
            # 打开文件
            self.wb = xlrd.open_workbook(file_name)
            # self.sheet_names = self.wb.sheet_names()
            # # 装载所有数据的list
            # self.list_data = []
            # # 将测试数据内调用的方法，改编成自定义里面的变量
            # self.dict_data = {}
        # 写入excel
        elif type == 'w':
            self.wb = xlsxwriter.Workbook(file_name)
        # 获取到所有的sheet_names,sheet1,sheet2获取到所有，获取到的是一个list
        self.sheets = self.wb.sheet_names()

    def read(self, sheet):
        #打开excel文档
        file = self.wb
        # 获取工作表
        table = file.sheet_by_name(sheet)
        row_Num = table.nrows   # 获取行数
        col_Num = table.ncols   # 获取列数
        list = []
        key = table.row_values(0)
        if row_Num <= 1:
            print("表中无数据")
            logging.error('表中无数据')
        else:
            # print("行数:", row_Num)
            # print("列数:", col_Num)
            j = 1
            # n 控制去除excel多少行的数据
            for i in range(row_Num-1):
                d = {}
                values = table.row_values(j)
                for x in range(col_Num):
                    d[key[x]] = values[x]
                j += 1
                list.append(d)
        return list

    # 获取表格行数和某行数据
    def read_row(self, sheet):
        # 打开excel文档
        file = self.wb
        # 获取工作表
        table = file.sheet_by_name(sheet)
        row_Num = table.nrows  # 获取行数
        col_Num = table.ncols  # 获取列数
        return row_Num

    # 获取某一行数据
    def row_value(self, sheet, row):
        # 打开excel文档
        file = self.wb
        # 获取工作表
        table = file.sheet_by_name(sheet)
        row_value = table.row_values(row)
        return row_value

#
#     def write(self, data, sheet_name):
#         # 设置报告格式
#         sheet = self.workbook.add_worksheet(sheet_name)
#         # 每行的宽度
#         sheet.set_column('A:Q', 15)
#         print(data)
#         for i in range(len(data)):
#             for j in range(len(data[i])):
#                 sheet.write(i, j, str(data[i][j]))
#         self.workbook.close()
#
#
# # 判断当前目录是否存在
# def mkdir(dir, filename):
#     path = Path('control'+dir)
#     # 如果文件不存在 则创建
#     if not path.is_dir():
#         path.mkdir()
#     if not os.path.isfile(filename):  # 无文件时创建
#         fd = open(filename, mode="w", encoding="utf-8")
#         fd.close()
#
# # 获取当前本地时间
# def gettime():
#     nowtime = time.strftime("%Y%m%d_%H:%M", time.localtime())
#     return nowtime


if __name__ == '__main__':

    a = '../testcase/testcase-smoking.xlsx'
    b = 'sheet1'
    r = Excel('r', a)
    print(r.sheets)
    print(r.read_row('sheet1'))
    print(r.row_value('sheet1', 1))
    # print(s)
    # print(type(s))
    # plist = []
    # for i in s:
    #     params = eval(i['params'])
    #     exp_res = i['Expected_res']
    #     # params_list.append(params)
    #     # res_list.append(exp_res)
    #     plist.append(i)
    # print(s)

#

